const fs = require('fs');
const path = require('path');
const { spawn } = require('child_process');
const connection = require('../../config/mysql.js');
require('dotenv').config();


// =======================
// 配置常量
// =======================
const SQL_DIR = path.join(__dirname, './sql');

// =======================
// 获取数据库配置
// =======================
function getDbConfig() {
    return {
        host: process.env.DB_HOST || 'localhost',
        user: process.env.DB_USER || 'root',
        password: process.env.DB_PASSWORD || '123456',
        database: process.env.DB_NAME || 'koa',
        port: process.env.DB_PORT || 3306,
    };
}

// =======================
// 初始化数据库：按顺序建表，跳过已存在的表
// =======================
async function initializeDatabase() {
    console.log('🚀 开始初始化数据库...');

    const creationOrder = [
        'maps',// 地图
        'entrances',// 传送门
        'accounts',// 账户
        'job_classes',// 职业
        'characters',// 角色
        'character_equipment',
        'character_inventory',
    ];

    // 获取所有 SQL 文件并排序
    if (!fs.existsSync(SQL_DIR)) {
        console.error(`❌ SQL目录不存在: ${SQL_DIR}`);
        return;
    }

    const sqlFiles = fs.readdirSync(SQL_DIR)
        .filter(file => file.endsWith('.sql'))
        .sort((a, b) => {
            const nameA = path.basename(a, '.sql');
            const nameB = path.basename(b, '.sql');
            const idxA = creationOrder.indexOf(nameA);
            const idxB = creationOrder.indexOf(nameB);
            if (idxA === -1 && idxB === -1) return nameA.localeCompare(nameB);
            if (idxA === -1) return 1;
            if (idxB === -1) return -1;
            return idxA - idxB;
        });

    if (sqlFiles.length === 0) {
        console.warn('⚠️ 未找到任何 .sql 文件');
        return;
    }

    // 用于记录结果
    const createdTables = [];
    const existingTables = [];

    // 获取连接并设置环境
    let conn;
    try {
        conn = await connection.getConnection();
        await conn.execute("SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';");
        await conn.execute("SET FOREIGN_KEY_CHECKS = 0;");
        await conn.execute("SET UNIQUE_CHECKS = 0;");
        await conn.execute("SET TIME_ZONE = '+00:00';");
        conn.release();
    } catch (err) {
        console.error('❌ 设置数据库环境失败:', err.message);
        throw err;
    }

    // 遍历每个文件
    for (const fileName of sqlFiles) {
        const tableName = path.basename(fileName, '.sql');
        const filePath = path.join(SQL_DIR, fileName);

        try {
            // 检查表是否已存在
            const checkConn = await connection.getConnection();
            const [rows] = await checkConn.query(
                `SELECT 1 FROM information_schema.tables WHERE table_schema = ? AND table_name = ?;`,
                [process.env.DB_NAME || 'koa', tableName]
            );
            checkConn.release();

            if (rows.length > 0) {
                existingTables.push(tableName);
                continue; // 跳过已存在的表
            }
        } catch (err) {
            console.error(`❌ 检查表存在性失败 (${tableName}):`, err.message);
            throw err;
        }

        // 表不存在，开始创建
        let sqlContent;
        try {
            sqlContent = fs.readFileSync(filePath, 'utf8');
        } catch (err) {
            console.error(`❌ 读取SQL文件失败: ${filePath}`, err.message);
            throw err;
        }

        const statements = sqlContent.split(/;\s*\n/).filter(s => s.trim());
        const createConn = await connection.getConnection();

        try {
            for (const sqlLine of statements) {
                let sql = sqlLine.trim() + ';';

                // 跳过不需要的语句
                if (
                    /SET @OLD_/i.test(sql) ||
                    /SET (SQL_MODE|TIME_ZONE|FOREIGN_KEY_CHECKS|UNIQUE_CHECKS|CHARACTER_SET_CLIENT|CHARACTER_SET_RESULTS|COLLATION_CONNECTION|SQL_NOTES)/i.test(sql) ||
                    /^(LOCK|UNLOCK)\s+TABLES/i.test(sql) ||
                    /^--/.test(sql) ||
                    sql.trim() === ';'
                ) {
                    continue;
                }

                // 统一字符集
                sql = sql
                    .replace(/CHARACTER SET \w+/gi, 'CHARACTER SET utf8mb4')
                    .replace(/CHARSET=\w+/gi, 'CHARSET=utf8mb4');

                try {
                    await createConn.execute(sql);
                } catch (err) {
                    const msg = err.message;
                    if (
                        msg.includes('not supported in the prepared statement protocol') ||
                        msg.includes('NULL') ||
                        msg.includes('timezone')
                    ) {
                        continue; // 忽略非关键错误
                    } else {
                        throw new Error(`执行失败: ${sql.substring(0, 100)}... | 错误: ${msg}`);
                    }
                }
            }

            createdTables.push(tableName);
        } catch (err) {
            console.error(`❌ 创建表 "${tableName}" 失败:`, err.message);
            throw err;
        } finally {
            createConn.release();
        }
    }

    // 恢复外键检查
    try {
        const conn = await connection.getConnection();
        await conn.execute("SET FOREIGN_KEY_CHECKS = 1;");
        conn.release();
    } catch (err) {
        console.warn('⚠️ 恢复外键检查失败:', err.message);
    }

    // 输出最终结果
    if (existingTables.length > 0) {
        console.log(`🟡 已存在的表 (${existingTables.length}): ${existingTables.join(', ')}`);
    }
    if (createdTables.length > 0) {
        console.log(`🟢 新建的表 (${createdTables.length}): ${createdTables.join(', ')}`);
    }
    if (createdTables.length === 0 && existingTables.length === 0) {
        console.log('📭 未处理任何表（SQL目录为空）');
    } else {
        console.log('🎉 数据库初始化完成！');
    }
}

// =======================
// 备份数据库（使用 spawn）
// =======================
async function backupDatabase() {
    const dbConfig = getDbConfig();
    const writtenFiles = [];

    try {
        if (!fs.existsSync(SQL_DIR)) {
            fs.mkdirSync(SQL_DIR, { recursive: true });
            console.log(`📁 创建备份目录: ${SQL_DIR}`);
        }

        const { host, user, password, database, port } = dbConfig;

        const mysqldumpArgs = [
            `--host=${host}`,
            `--port=${port}`,
            `--user=${user}`,
            `--password=${password}`,
            '--single-transaction',
            '--routines',
            '--triggers',
            '--set-gtid-purged=OFF',
            '--complete-insert',
            '--add-drop-table',
            '--default-character-set=utf8mb4',
            database
        ];

        const dumpProcess = spawn('mysqldump', mysqldumpArgs);

        let stdout = '';
        let stderr = '';

        dumpProcess.stdout.on('data', (data) => {
            stdout += data.toString();
        });

        dumpProcess.stderr.on('data', (data) => {
            const msg = data.toString();
            if (!msg.includes('Using a password')) {
                console.warn('⚠️ mysqldump stderr:', msg.trim());
            }
            stderr += msg;
        });

        const exitCode = await new Promise((resolve) => {
            dumpProcess.on('close', resolve);
        });

        if (exitCode !== 0) {
            throw new Error(`mysqldump 退出码: ${exitCode}, 错误: ${stderr}`);
        }

        if (!stdout.trim()) {
            throw new Error('mysqldump 输出为空，可能连接失败或无数据');
        }

        // 解析 SQL 按表分割（简单版：基于 DROP TABLE IF EXISTS）
        const tableRegex = /--\s*Table structure for table `?(\w+)`?[\s\S]*?(?=--\s*Table structure|$)/g;
        const tables = [];
        let match;

        while ((match = tableRegex.exec(stdout)) !== null) {
            const tableName = match[1];
            const start = match.index;
            const nextMatch = tableRegex.lastIndex;
            const sql = stdout.slice(start, nextMatch).trim();

            tables.push({ tableName, sql });
        }

        if (tables.length === 0) {
            console.error('❌ 未能解析出任何表结构');
            return;
        }

        // 写入文件
        for (const { tableName, sql } of tables) {
            const finalSql = sql
                .replace(/CHARACTER SET \w+/gi, 'CHARACTER SET utf8mb4')
                .replace(/CHARSET=\w+/gi, 'CHARSET=utf8mb4');

            const filePath = path.join(SQL_DIR, `${tableName}.sql`);
            fs.writeFileSync(filePath, finalSql, 'utf-8');
            writtenFiles.push(tableName);
        }

        console.log(`✅ 备份完成！共 ${writtenFiles.length} 张表已保存: ${writtenFiles.join(', ')}`);
        return tables;
    } catch (error) {
        console.error('❌ 备份失败:', error.message);
        throw error;
    }
}

// =======================
// 导出函数
// =======================
module.exports = {
    initializeDatabase,
    backupDatabase
};